（高级篇）MySQL 并发控制—— MVCC 与锁

发表于 | 更新于 | 分类于 | 阅读次数：
本文字数： 16k | 阅读时长 ≈ 23 分钟

序言

  从前文我们知道，在事务运行过程中，数据库设置的隔离级别不同，解决的并发问题也不同。
  那么思考一个问题：这些隔离级别在内部到底是如何解决并发问题的呢？
  在数据库系统中，是通过 MVCC 和锁机制来解决该问题的。

  那么，下面来详细了解下它们吧！

MVCC

  MVCC（Multi-Version Concurrency Control），即多版本并发控制，指在RC、RR两种隔离级别下，事务在执行普通的 SELECT 操作时通过 ReadView 快照访问 undolog 版本链查找可见数据的过程。

作用

  MVCC 用于解决一些并发问题，最终提高并发情况的性能：

  • 读-读冲突：在传统的锁机制下，多个事务可以同时读取同一行数据，但是如果有一个事务正在修改数据，则其他事务的读操作应该等待。MVCC 允许多个事务并发读取数据的不同版本，从而解决了读-读冲突的问题，提高了并发性能
  • 读-写冲突：在传统的锁机制下，读操作和写操作会发生冲突，即一个事务正在写入数据时，其他事务无法读取该数据。这会导致并发性能下降，MVCC 通过允许读操作与写操作并发进行，解决了读-写冲突的问题
  • 写-写冲突：传统的锁机制还会导致写操作之间的冲突，即多个事务同时试图修改同一行数据，只有一个事务能成功，其他事务需要等待。MVCC 通过使用版本号或时间戳来跟踪数据的不同版本，每个事务操作的是数据的一个特定版本，从而解决了写-写冲突的问题

  MVCC 支持一致性读取，即读取操作会看到一个一致性的数据库快照。读操作会根据事务开始的时间或快照版本确定可见的数据版本，而不会受到后续并发事务的修改影响。这样可以保证读操作的一致性，避免了脏读或不可重复读等问题。

数据库差异

  不仅是 MySQL，包括 Oracle、PostgreSQL 等其他数据库系统也都实现了 MVCC，但各自的实现机制不尽相同，因为 MVCC 没有一个统一的实现标准，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

原理

  使用版本链。

版本链

  版本链是数据修改时出现的对应数据的一串 undo-log 链表。
  下面，我们通过一个例子了解它。

快速入门

  对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列：

  • trx_id：每次对某条聚簇索引记录进行改动时，都会把对应的事务 id 赋值给trx_id隐藏列。
  • roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到undo日志中，该隐藏列相当于一个指针，通过它可以找到该记录修改前的信息。

  下面的t表现在只包含一条记录：

1
2
3
4
5
6
7
mysql> SELECT * FROM t;
+----+--------+
| id | c      |
+----+--------+
|  1 | 刘备 |
+----+--------+
1 row in set (0.01 sec)

  假设插入该记录时的trx_id（事务 id）为 80，那么此刻该条记录的示意图如下所示：

  假设之后两个trx_id分别为 100、200 的事务对这条记录进行 UPDATE 操作，操作流程如下表：

时间 trx_id:100 trx_id:200
T1 BEGIN;
T2 BEGIN;
T3 UPDATE t SET u = '关羽' WHERE id = 1
T4 UPDATE t SET u = '张飞' WHERE id = 1
T5 COMMIT;
T6 UPDATE t SET u = '赵云' WHERE id = 1
T7 UPDATE t SET u = '诸葛亮' WHERE id = 1
T8 COMMIT;

  每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性，将这些undo日志都连起来就形成了一个链表，所以现在的情况就像下图一样：

  版本链的头节点存储了当前记录的最新值，每次对该记录更新后，都会将旧值放到一条undo日志中，即该记录的一个旧版本。随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，这个链表就被称之为版本链。

  另外，每个版本中还包含生成该版本时对应的trx_id，这个信息很重要，因为我们稍后就会用到。

ReadView

  对于不同隔离级别的事务来说，读取记录的方式也不同：

隔离级别 读取记录方式
READ UNCOMMITTED 直接读取记录的最新版本
READ COMMITTED 使用版本链
REPEATABLE READ 使用版本链
SERIALIZABLE 加锁后访问记录

  RC和RR都使用到了版本链，相同点是它们都需要借助ReadView判断版本链中的哪个版本为当前事务可见，不同点的是它们ReadView是否能重复生成。

  ReadView即读快照，其中主要存储了当前系统中还活跃的读写事务，这些活跃事务的trx_id将放到一个名为m_ids的列表中。
  因此，在访问某条记录时，只需要按以下步骤即可判断记录的某个版本是否可见：

  • 若被访问版本的trx_id属性值小于m_ids列表中最小的事务 id，表明生成该版本的事务在生成ReadView前已经提交，因此该版本可以被当前事务访问
  • 若被访问版本的trx_id属性值大于m_ids列表中最大的事务 id，表明生成该版本的事务在生成ReadView后才生成，因此该版本不可以被当前事务访问
  • 若被访问版本的trx_id属性值在m_ids列表中最大的事务 id 和最小事务 id 之间，那就需要判断一下trx_id属性值是不是在m_ids列表中：
    • 若存在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可被访问
    • 若不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问

  如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。
  如果最后一个版本也不可见的话，那么就意味着该条记录对该事务不可见，查询结果就不包含该记录。

  在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同，我们来看一下。

RC 场景

  在READ COMMITTED隔离级别下，每次读取数据前都将生成一个ReadView。
  我们先回忆一下，该隔离级别解决了什么并发问题。
  脏读，没错，该隔离级别不允许脏读。
  那么为何能解决脏读呢？
  因为未提交（被回滚）的事务操作涉及的数据记录对其他事务不可见。
  下面通过一个例子来解释一下：
  比方说现在系统里有两个trx_id分别为 100、200 的事务正在执行：

1
2
3
4
5
6
# trx_id 100
BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;
1
2
3
4
5
# trx_id 200
BEGIN;

# 更新了一些别的表的记录
...

  此刻，t表中 id 为1 的记录得到的版本链表如下所示：

  假设现在有一个使用READ COMMITTED隔离级别的事务开始执行：

1
2
3
4
5
# 使用 READ COMMITTED 隔离级别的事务
BEGIN;

# SELECT 1：trx_id 为 100、200 未提交
SELECT * FROM t WHERE id = 1; # 得到的列 c 的值为'刘备'

  SELECT 1的执行过程如下：

  • 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表内容为[100, 200]
  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列c的内容是'张飞'，该版本的trx_id值为100，存在于m_ids列表，因此不符合可见性要求，根据roll_pointer跳到下一个版本
  • 下一个版本的列c的内容是'关羽'，该版本的trx_id值也为100，也在m_ids列表内，因此也不符合要求，继续跳到下一个版本。
  • 下一个版本的列c的内容是'刘备'，该版本的trx_id值为80，小于m_ids列表中最小的trx_id值100，因此这个版本是符合要求的，那么最后返回给用户的版本就是这条列c为'刘备'的记录

  这是事务 100 和 200 还未提交时的 SELECT情况，现在我们仅仅提交一下事务 100 ：

1
2
3
4
5
6
7
8
# trx_id 100
BEGIN;

UPDATE t SET c = '关羽' WHERE id = 1;

UPDATE t SET c = '张飞' WHERE id = 1;

COMMIT;

  然后在trx_id为 200 的事务中更新一下t表中 id 为 1 的记录：

1
2
3
4
5
6
7
8
# trx_id 200
BEGIN;

# 更新了一些记录

UPDATE t SET c = '赵云' WHERE id = 1;

UPDATE t SET c = '诸葛亮' WHERE id = 1;

  此刻，t表中 id 为 1 记录的版本链就长这样：

  然后再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个 id 为 1 的记录：

1
2
3
4
5
6
7
8
# 使用 READ COMMITTED 隔离级别的事务
BEGIN;

# SELECT 1：trx_id 100、200 均未提交
SELECT * FROM t WHERE id = 1; # 得到的列 c 的值为'刘备'

# SELECT 2：trx_id 100 提交，trx_id 200 未提交
SELECT * FROM t WHERE id = 1; # 得到的列 c 的值为'张飞'

  SELECT 2的执行过程如下：

  • 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[200]（trx_id为100的那个事务已经提交了，所以生成快照时就没有它了）
  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列c的内容是'诸葛亮'，该版本的trx_id值为200，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本
  • 下一个版本的列c的内容是'赵云'，该版本的trx_id值为200，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本
  • 下一个版本的列c的内容是'张飞'，该版本的trx_id值为100，比m_ids列表中最小的trx_id值200还要小，所以这个版本是符合要求的，最后返回给用户的版本就是这条列c为'张飞'的记录

  以此类推，如果之后trx_id为200的记录也提交了，再此在使用READ COMMITTED隔离级别的事务中查询表t中id值为1的记录时，得到的结果就是'诸葛亮'了，具体流程我们就不分析了。
  总结一下就是：使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

RR 场景

  在REPEATABLE READ隔离级别下，只会在第一次执行查询语句时生成一个ReadView，之后的查询不会重复生成。由于在同一个事务中，多次SELECT操作只会生成一个ReadView，所以自然就解决了不可重复读下的数据重复读取问题。

小结

  MVCC 只在RC、RR两个隔离级别下工作，都通过ReadView来判断事务版本信息，只会读取版本链中的最新已提交事务的 undo 日志。
  MVCC 可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
  RC、RR两个隔离级别的一个很大不同就是生成ReadView的时机不同：

  • RC会在每一次进行普通SELECT操作前都生成一个ReadView
  • RR只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复该ReadView

  其他两个隔离级别都和 MVCC 不兼容，因为RU总是读取最新的数据行，而不是符合当前事务版本的数据行；SERIALIZABLE则会对所有读取的行都加锁。

扩展——查询再插入再查询操作下的数据读取数

  思考一个问题，在 RR 隔离级别下，如果一种表存在 4 条数据，我们开启一个事务后执行以下操作：

  • ① 查询全表数据
  • ② 插入一条数据
  • ③ 查询全表数据

  那么，第 ③ 步会查询到几条数据？

  答案是 5 条数据。

  具体过程如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM test;
+----+------+
| a  | b    |
+----+------+
|  1 |    4 |
|  6 |    5 |
|  7 |    5 |
| 17 |    5 |
+----+------+
4 rows in set (0.00 sec)

mysql> INSERT INTO `osier`.`test` (`a`, `b`) VALUES (20, 5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+------+
| a  | b    |
+----+------+
|  1 |    4 |
|  6 |    5 |
|  7 |    5 |
| 17 |    5 |
| 20 |    5 |
+----+------+
5 rows in set (0.00 sec)

为什么呢？

这其实是对同一事务处理数据的不同导致的。

在 MySQL 的 RR（Repeatable Read）隔离级别下，通常情况下，事务在读取数据时会生成一个一致性读视图（consistent read view），这个视图在事务开始时固定，即所谓的“快照”，因此再次读取相同的数据集时应看到相同的结果。然而，在上面描述的场景中，似乎在同一事务中插入了一条新记录，并在后续的 SELECT 语句中可见。

这种现象并不违反 RR 隔离级别的规则，因为：

自身修改的可见性：在 RR 隔离级别下，一个事务能够看见自己对数据的修改（包括插入、更新和删除）。虽然一致性读视图保证了在事务进行期间从其他并发事务中读取到的一致数据，但是事务自身的修改对自身是可见的。

插入操作的例外：当你在一个事务内插入一条新的记录时，这条记录会立即对该事务可见。这意味着虽然您在第一次 SELECT 语句中获得了一个一致性快照，但是在同一个事务中插入的一条新记录对于该事务的后续查询是可见的。

具体到的 SQL 操作流程：

开启事务，并执行第一次 SELECT 获得了初始一致性快照，此时显示 4 条记录。
执行 INSERT 操作插入一条新的记录（a 为 20，b 为 5）。
再次执行 SELECT 时，虽然 RR 隔离级别保证了其他事务对数据的修改对当前事务是不可见的，但这个 INSERT 操作确实是在当前事务中执行的，因此这条新插入的记录对于当前事务是可见的，所以此次查询返回了5条记录。

所以，简而言之，RR 隔离级别中的一致性视图规则适用于其他事务的修改，但不适用于当前事务自身的修改。当前事务始终能够看到它自己所做的所有修改包括新增的数据。

InnoDB 中的读

  在 MySQL 中，支持三种类型的读语句：

  • 一致性（无锁）读
  • 半一致性读
  • 锁定读

不同类型的读

一致性（无锁）读

   一致性（无锁）读，亦称普通读，快照读，英文名：Consistent Read）：指普通的SELECT读语句，即在SELECT语句末尾不加FOR UPDATE或者LOCK IN SHARE MODE的SELECT语句。

  普通读的执行方式是生成ReadView直接利用 MVCC 机制来进行读取数据，并不会对记录进行加锁。

注意事项

  注意哦，一致读取对某些 DDL 语句不起作用，比如说：

  • 对DROP TABLE操作不起作用，因为 MySQL 无法使用已被丢弃的表，而且 InnoDB 会销毁表
  • 对ALTER TABLE操作不起作用，因为 ALTER TABLE 操作会创建原始表的临时副本，并在创建临时副本时删除原始表。在事务中重新发出一致读时，新表中的行不可见，因为在事务快照时这些行还不存在。在这种情况下，事务会返回错误：ER_TABLE_DEF_CHANGED，”表定义已更改，请重试事务”

  对于 INSERT INTO … 等子句中的选择，读取类型有所不同。select、update …(SELECT) 和 CREATE TABLE …SELECT 等子句中的选择类型有所不同，这些子句没有指定 FOR UPDATE 或 LOCK IN SHARE MODE：

  • 默认情况下，InnoDB 会在这些语句中使用更强的锁，SELECT 部分的读取行为类似于 RC 下的 MVCC，在这种情况下，即使在同一个事务中，每次一致的读取都会设置并读取自己的新快照
  • 要在这种情况下执行无锁读取，请启用innodb_locks_unsafe_for_binlog选项，并将事务的隔离级别设置为 RU、RC 或 RR，以避免对从所选表读取的记录设置锁

半一致性读

  半一致性读（英文名：Semi-Consistent Read）：这是一种夹在普通读和锁定读之间的一种读取方式。它只在READ COMMITTED隔离级别下（或者在开启了innodb_locks_unsafe_for_binlog系统变量的情况下）使用UPDATE语句时才会使用。具体的含义就是当UPDATE语句读取已经被其他事务加了锁的记录时，InnoDB会将该记录的最新提交的版本读出来，然后判断该版本是否与UPDATE语句中的WHERE条件相匹配，如果不匹配则不对该记录加锁，从而跳到下一条记录；如果匹配则再次读取该记录并对其进行加锁。这样子处理只是为了让UPDATE语句尽量少被别的语句阻塞。

  注意：半一致性读只适用于对聚簇索引记录加锁的情况，并不适用于对二级索引记录加锁的情况。

  另外，MySQL 官方文档并没有对半一致性读的单独说明，算是一致性读的一种特例。

锁定读

  锁定读（英文名：Locking Read）：指SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE这种读语句，在事务读取记录之前，必须先获取该记录对应的锁。当然，获取什么类型的锁取决于当前事务的隔离级别、语句的执行计划、查询条件等因素。

注意事项

  外层语句中的锁定读取子句不会锁定嵌套子查询中表的记录，除非在子查询中也指定了锁定读取子句。

  例如，以下语句不会锁定表 t2 中的记录。

1
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

  因此，若要锁定表 t2 中的记录，请在子查询中也添加锁定读取子句：

1
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

读在不同隔离级别的转换

  对于SERIALIZABLE隔离级别来说，如果系统变量autocommit被设置为OFF，那普通读的语句会转变为锁定读，和在普通的SELECT语句后边加LOCK IN SHARE MODE达成的效果一样。

疑问：RR 隔离级别下真的能避免幻读嘛？

  我们在讨论问题之前，先回忆下幻读是什么。
  幻读指某个事务突然看到了一个它以前没有见过的数据行。比如说，若某个事务刚执行完一条SELECT语句就有另一个事务插入了一个新数据行，前一个事务再次执行同一条SELECT语句时，就可能多看到一个新的数据行，这就是幻读。

  在 RR 隔离级别下，一致性读看到是事务开始时的快照，即使其它事务插入了新行通常也是看不到的，所以在常见的场合可以避免幻读。 但是，对于执行锁定读、更新或者删除语句时，还是会看到数据库的最新状态，比如新插入的数据行，修改的数据行。

  下面通过一个例子来复现这种情况：

SESSION 1:

1
2
3
4
5
6
7
8
9
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
+----+------+
1 row in set (0.00 sec)

SESSION 2:

1
2
3
mysql> update tb1 set c1=101 where id =1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SESSION 1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select id,c1 from tb1 where id=1 LOCK IN SHARE MODE;
+----+------+
| id | c1   |
+----+------+
|  1 |  101 |
+----+------+
1 row in set (0.00 sec)

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 |  100 |
+----+------+
1 row in set (0.00 sec)

mysql> update tb1 set c1=c1+1000 where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select id,c1 from tb1 where id=1;
+----+------+
| id | c1   |
+----+------+
|  1 | 1101 |
+----+------+
1 row in set (0.00 sec)

  上面 update 的行为违反了 RR 的承诺，看到了事务开始后其它事务的并发更新。这对应用开发需要特别注意，这种情况下其它数据库通常都是报错的。

  扯了这么多，我们如何避免遇到这个并发更新问题呢？

  答案很简单，修改时加WHERE条件。

InnoDB 中的锁

  在 MySQL InnoDB 存储引擎中，锁大致可以进行以下划分：

  • 全局锁\表锁\行锁
  • 共享Shared锁和排他Exclusive锁
  • 意向Intention锁
  • 记录Record锁
  • 间隙Gap锁
  • 临键Next-Key锁
  • 插入意向Insert Intention锁
  • 自增AUTO-INC锁
  • 空间索引的谓词锁（略）

全局锁\表锁\行锁

  从锁的粒度维度，可以分成表锁与行锁两大类：

类别 说明 特点
全局锁 对整个 MySQL 实例加锁 主要用于库备份
表锁 对查询的整张表加锁 开销小，加锁快；不会出现死锁；
锁定力度大，发生锁冲突概率高，并发度最低
行锁 对查询出的行加锁 开销大，加锁慢；会出现死锁；
锁定粒度小，发生锁冲突的概率低，并发度高

  当然，不同的存储引擎支持的锁粒度不同：

  • MyISAM 只支持表锁

  • InnoDB 既支持行锁又支持表锁

全局锁

  顾名思义，全局锁就是对整个数据库实例加锁。

  MySQL 提供了一个加全局读锁的方法，命令是：

1
FLUSH TABLES WITH READ LOCK;

  全局锁的典型使用场景是，做全库逻辑备份。因此，当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：

  • 数据更新语句（数据的增删改）
  • 数据定义语句（包括建表、修改表结构等）
  • 更新类事务的提交语句。

表锁

  MySQL 里面表级别的锁有两种：一种是显式的表锁，一种是隐式的元数据锁（meta data lock，MDL)。

显式表锁

  如果要显式的对表进行加锁，相关语法为lock tables … read/write。可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

  举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

隐式的 MDL 锁

  MDL 锁，即元数据锁（meta data lock)，MDL 锁不需要显式使用，在访问一个表的时候会被自动加上。

  MDL 锁的作用是保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

  因此，在 MySQL 5.5 版本中引入了 MDL：

  • 当对一个表做增删改查操作的时候，加 MDL 读锁；
  • 当要对表做结构变更操作的时候，加 MDL 写锁。

  其中：

  • 读锁与读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
  • 读写锁之间、写锁之间是互斥的。这是用来保证变更表结构操作的安全性，因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

行锁

  略。

共享锁和排他锁

  InnoDB 实现了标准的行级锁，有两种类型的锁，共享（S）锁和排他（X）锁。

  • 共享（S）锁：允许持有该锁的事务读取一条记录，多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改；
  • 排他（X）锁：允许持有该锁的事务更新或删除一条记录，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对数据行进行读取和修改

  如果事务 T1 持有 r 行的共享（S）锁，那么来自不同事务 T2 对 r 行的锁的请求将被如下处理：

  • T2 对 S 锁的请求可以被立即批准。结果是，T1 和 T2 都持有 r 的 S 锁
  • T2 对 X 锁的请求不能被立即批准

  如果一个事务 T1 在行 r 上持有一个排他（X）锁，那么某个不同的事务 T2 对 r 的任一类型的锁的请求不能立即被批准。相反，事务 T2 必须等待事务 T1 释放其对 r 行的锁。

S 锁加锁释锁示例

加锁方式：SELECT * FROM STUDENT where id = 1 LOCKINSHARE MODE;

释放锁：commit/rollback;

X 锁加锁释锁示例

  • 自动：INSERT、DELETE、UPDATE默认加上 X 锁；
  • 手动：SELECT * FROM STUDENT WHERE id = 1 FOR UPDATE；

意向锁

  InnoDB支持多粒度锁，允许行锁和表锁共存。例如，一个诸如LOCK TABLES ...WRITE这样的语句在指定的表上取得了一个排他锁（一个X锁）。为了使多个粒度级别的锁切实可行，InnoDB 使用意向锁。

  意向锁是表级别的锁，它表明事务以后需要对表中的某一行进行哪种类型的锁（共享或排他）。

  在 MySQL 中，存在两种类型的意向锁：

  • 意向共享锁（IS）：表示事务打算对表中的个别行设置一个共享锁
  • 意向排他锁（IX）：表示一个事务打算在表中的个别行上设置一个排他锁

  例如，SELECT ... LOCK IN SHARE MODE设置了一个 IS 锁，SELECT ... FOR UPDATE设置了一个 IX 锁。

  意向锁协议如下：

  • 在一个事务可以获得表内某行的共享锁之前，它必须首先获得该表的 IS 锁或更强的锁
  • 在一个事务可以获得表中某行的排他锁之前，它必须首先获得该表的 IX 锁

  表级的锁类型兼容性总结在下面的矩阵中。

X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

  如果一个锁与现有的锁兼容，就会授予请求的事务，但如果与现有的锁冲突，就不会授予。一个事务会等待，直到与之冲突的现有锁被释放。如果一个锁请求与现有的锁冲突，并且不能被授予，因为它会导致死锁，那么就会发生一个错误。

  除了全表请求（例如，LOCK TABLES … WRITE），意向锁不会阻止任何东西。意向锁的主要目的是显示有人正在锁定某一行，或将要锁定表中的某一行。

  在SHOW ENGINE INNODB STATUS和InnoDB监控输出中，意向锁的事务数据显示类似于以下内容：

1
TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

Q：为什么有意向锁？

  意向锁存在原因主要在两点：

  • 防止锁冲突和死锁
  • 提高并发性能

提高并发性能

  意向锁可以提高并发操作的性能，啥意思呢？我们可以先讨论个问题。
  如果一个数据行已被一个线程加锁处理中了，此时再来了一个新线程，那么新线程正常情况下会怎么执行自己的后续步骤呢？
  答案很简单，尝试对数据行加锁，但是会发现数据行已被加锁，那么阻塞等待。
  那么，经过以上一个过程，我们可以再思考一个问题：如果发现一个数据行已被加锁，后续线程可不可以直接阻塞等待，不要去尝试对数据行加锁，反正也抢不到锁，这么做可以减少一个步骤。
  当然，可以，那么如何做呢？
  需要一个标志来标明数据行已被加锁，而意向锁就是标志来确定是否存在的。

  打个比方，我们在节假日聚会时，会提前预定好餐厅的包间，如果没预定到，那么就不去现场了，预定包间 –> 去预定的包间现场是两个顺序时间事件，如果没预定到包间，那么就不用去现场了，不然是浪费时间。

防止锁冲突和死锁

  在没有意向锁的情况下，行锁和表锁之间可能会产生冲突。例如，一个事务在表上加了锁，而另一个事务试图在表中的某些行上加锁，这样会导致死锁或阻塞。

  意向锁通过在表级和行级之间设置一种明确的锁定意图，可以有效地避免这种情况。它让数据库知道某个事务打算在某个级别上获取锁，避免了重复的锁检查和潜在的死锁。

Q：需要关注 S 锁嘛？

  一般关注 X 锁即可，因为 S 锁基本不会显式用到，只有在Serializable隔离级别下，所有的SELECT语句会被隐式的转化为SELECT ...LOCK IN SHARE MODES，此时与UPDATE、DELETE相关 SQL 互斥。

Record 锁（记录锁）

  记录锁是对一个索引记录的锁，在使用精准匹配处理数据时会使用到。

  例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;语句可以防止任何其他事务插入、更新或删除t.c1的值为10的记录。

  记录锁总是锁定聚集索引的对应记录，即使一个表被定义为没有索引。在这种情况下，InnoDB 会创建一个隐藏的聚集索引，并使用这个索引来锁定记录。

  记录锁的事务数据在SHOW ENGINE INNODB STATUS和 InnoDB 监控输出中显示类似于以下内容：

1
2
3
4
5
6
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Gap 锁（间隙锁）

  间隙锁是对索引记录之间的间隙的锁，或者对第一个或最后一个索引记录之前的间隙的锁。

  例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;语句可以防止其他事务在列t.c1中插入一个15的值，无论该列中是否已经有这样的值，因为该范围中所有现有值之间的空隙被锁定。

  一个间隙可能横跨一个索引值，多个索引值，甚至是空的。

  间隙锁是性能和并发性之间权衡的一部分，并且只在一些事务隔离级别中使用。

  对于使用唯一索引锁定行以搜索唯一行的语句，不需要间隙锁。(这不包括搜索条件只包括多列的某些列的情况。

  在 locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE)，UPDATE 和 DELETE 时，锁定取决于 SQL 语句是否是使用具有唯一搜索条件的唯一索引，或者范围类型的搜索条件：

  • 对于具有唯一搜索条件的唯一索引，InnoDB 只锁定找到的索引记录，不锁定之前的间隙
  • 对于其他搜索条件，InnoDB 锁定扫描的索引范围，使用间隙锁或临键锁来阻止插入由其他 Session 填补范围内覆盖的间隙

Next-Key 锁（临键锁）

  next-key lock 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

InnoDB以这样的方式执行行级锁定：当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享或排他锁。因此，行级锁实际上是索引记录锁。索引记录上的下一个键锁也会影响该索引记录之前的“间隙”。也就是说，下一个键锁是索引记录锁加上索引记录前面间隙上的间隙锁。如果一个会话对索引中的记录具有共享或排他锁 ，则另一个会话不能在索引顺序中R`紧接在之前的间隙中插入新的索引记录

  假设一个索引包含值 10、11、13 和 20。该索引的可能的 next-key 锁涵盖以下区间，其中圆括号表示排除区间端点，方括号表示包含端点：

1
2
3
4
5
(负无穷, 10]
(10, 11]
(11, 13]
(13, 20]
(20, 正无穷)

  对于最后一个间隔，next-key 锁会锁定索引中最大值上方的间隙以及“ supremum ”（正无穷）为记录，该记录的值高于索引中实际的任何值。 supremum 不是真正的索引记录，因此，实际上，此 next-key 锁仅锁定最大索引值后面的间隙。

  SHOW ENGINE INNODB STATUSNext-Key 键锁的事务数据将输出类似于以下内容 ：

1
2
3
4
5
6
7
8
9
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

插入意向锁

  插入意向锁是一种由 INSERT 操作在行插入前设置的间隙锁。这种锁发出了插入意向的信号，如果多个事务在同一索引间隙中插入的位置不一样，就不需要互相等待。

  假设有数值为 4 和 7 的索引记录。分别试图插入值为 5 和 6 的事务，在获得插入行的排他锁之前，各自用插入意向锁（间隙锁）锁定了 4 和 7 之间的间隙，但是由于这些行是不冲突的，所以不会互相阻塞。

观察插入意向锁

  下面的例子演示了一个事务在获得被插入记录的排他锁之前，采取插入意向锁。这个例子涉及两个客户，A 和 B。

  客户端 A 创建了一个包含两个索引记录（90 和 102）的表，然后启动一个事务，对 ID 大于 100 的索引记录加了一个排他锁。该排他锁包括 102 号记录前的间隙锁：

1
2
3
4
5
6
7
8
9
10
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

  客户端 B 开始一个事务，向缺口插入一条记录。该事务在等待获得排他锁的同时，采取了一个插入意向锁。

1
2
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

  在 SHOW ENGINE INNODB STATUS 和 InnoDB 监控输出中，插入意向锁的事务数据显示类似于以下内容：

1
2
3
4
5
6
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

AUTO-INC 锁

  AUTO-INC 锁是一个特殊的表级锁，由插入有 AUTO_INCREMENT 列的表的事务使用。在最简单的情况下，如果一个事务正在向表中插入数值，任何其他事务必须等待向该表进行自己的插入，这样第一个事务插入的行就会得到连续的主键值。

  innodb_autoinc_lock_mode变量控制用于自动增量锁定的算法。它允许你选择如何在可预测的自动递增值序列和插入操作的最大并发性之间进行交换。

死锁

  当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

  当出现死锁以后，有两种策略：

  • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  • 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

  在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

  但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

  所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

处理方案

你可以想象一下这个过程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

那如果是我们上面说到的所有事务都要更新同一行的场景呢？

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

根据上面的分析，我们来讨论一下，怎么解决由这种热点行更新导致的性能问题呢？问题的症结在于，死锁检测要耗费大量的 CPU 资源。

一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。

另一个思路是控制并发度。根据上面的分析，你会发现如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。一个直接的想法就是，在客户端做并发控制。但是，你会很快发现这个方法不太可行，因为客户端很多。我见过一个应用，有 600 个客户端，这样即使每个客户端控制到只有 5 个并发线程，汇总到数据库服务端以后，峰值并发数也可能要达到 3000。

因此，这个并发控制要做在数据库服务端。如果你有中间件，可以考虑在中间件实现；如果你的团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

扩展——封锁粒度与封锁协议

  数据库中为了实现并发控制而采用封锁技术。封锁对象的大小称为封锁粒度（ Granularity ）。

  封锁的对象可以是逻辑单元，亦可以是物理单元。
  以关系数据库为例，封锁对象可以为以下逻辑单元：

  • 属性值
  • 属性值的集合
  • 元组
  • 关系
  • 索引项
  • 整个索引项
  • 甚至整个数据库

  封锁对象亦可以为以下物理单元：

  • 页（数据页或索引页）
  • 物理记录等

  在运用 X 锁和 S 锁对数据对象加锁时，还需要约定一些规则。比如何时申请 X 锁 或 S 锁、持锁时间多久、何时释放等规则，这些规则被称为为封锁协议（ Locking Protocol ）。
  对封锁方式规定的不同规则，形成了不同的封锁协议，而不同的封锁协议又对应不同的隔离级别。

两阶段锁协议

  在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

参考

文章信息

时间 说明
2022-02-18 初稿
0%